﻿-- DELETE ALL OBJECTS
CREATE PROCEDURE [todoc].[sspDeleteAllObjectsOfType]
(
@ObjectType NVARCHAR(20)
)
--WITH EXECUTE AS 'todocuser'
AS
/*
DELETES ALL TODOC OBJECTS OF A GIVEN TYPE FROM THE DATABASE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
SET NOCOUNT ON;

-- reject calls to delete anything we don't know about
IF (@ObjectType NOT IN('DeleteSP', 'InsertSP', 'SaveSP', 'Shadow', 
	'UpdateSP', 'View', 'DeleteTrigger', 'UpdateTrigger', 'InsertTrigger'))
BEGIN
	PRINT 'The @ObjectType parameter must be one the following values: DeleteSP, InsertSP, SaveSP, Shadow, UpdateSP, View, DeleteTrigger, UpdateTrigger, or InsertTrigger';
	RETURN;
END

DECLARE @SQL_DROP NVARCHAR(MAX) = '';
DECLARE @TableSchema SYSNAME;
DECLARE @TableName SYSNAME;

BEGIN TRANSACTION;

BEGIN TRY

	DECLARE TodocTables CURSOR
	READ_ONLY FORWARD_ONLY LOCAL STATIC
	FOR
	SELECT	nht.TableSchema, 
			nht.TableName
	FROM [todoc].[svNonHeapTables] nht
	INNER JOIN [todoc].[svActiveTables] at
		ON nht.[schema_id] = at.[schema_id]
		AND nht.[object_id] = at.[object_id]
	ORDER BY nht.TableSchema, nht.TableName;

	OPEN TodocTables

	PRINT 'Deleting todoc ' + @ObjectType + ' for:'

	FETCH NEXT FROM TodocTables INTO @TableSchema, @TableName
	WHILE (@@fetch_status <> -1)
	BEGIN
		IF (@@fetch_status <> -2)
		BEGIN

			PRINT @TableSchema + '.' + @TableName;

			-- call the relevant delete function based on @ObjectType and execute
			IF (@ObjectType = 'DeleteSP')
			BEGIN
				EXEC [todoc].[sspDropDeleteSP] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'InsertSP')
			BEGIN
				EXEC [todoc].[sspDropInsertSP] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'SaveSP')
			BEGIN
				EXEC [todoc].[sspDropSaveSP] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'Shadow')
			BEGIN
				EXEC [todoc].[sspDropShadowTable] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'UpdateSP')
			BEGIN
				EXEC [todoc].[sspDropUpdateSP] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'View')
			BEGIN
				EXEC [todoc].[sspDropView] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'DeleteTrigger')
			BEGIN
				EXEC [todoc].[sspDropDeleteTrigger] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'UpdateTrigger')
			BEGIN
				EXEC [todoc].[sspDropUpdateTrigger] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END
			ELSE IF (@ObjectType = 'InsertTrigger')
			BEGIN
				EXEC [todoc].[sspDropInsertTrigger] 
					@TableSchema, 
					@TableName,
					@Debug = 0
			END

		END
		FETCH NEXT FROM TodocTables INTO @TableSchema, @TableName
	END

	CLOSE TodocTables
	DEALLOCATE TodocTables;

	COMMIT TRANSACTION;

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	ROLLBACK TRANSACTION;

	RETURN -1;

END CATCH;